package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.ClientePersonasFisicasDao;
import com.sophossystems.client.asmon.dto.ClientePersonasFisicasDTO;
import com.sophossystems.client.asmon.dto.ClientePersonasFisicasPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.ClientePersonasFisicas;

public class ClientePersonasFisicasDaoImpl extends AbstractDAO implements ClientePersonasFisicasDao
{
	
	
	private static Logger log = Logger.getLogger(ClientePersonasFisicasDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public ClientePersonasFisicasPkDTO insert(
			final ClientePersonasFisicasDTO dto){
		ClientePersonasFisicasPkDTO exito =null ;
	try {
		 exito = (ClientePersonasFisicasPkDTO) getHibernateTemplate().execute(new HibernateCallback() {
			/**
			 * {@inheritDoc}
			 * 
			 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
			 */
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
			    Transaction tx = session.beginTransaction();
			    ClientePersonasFisicasPkDTO pk = null;
			    ClientePersonasFisicas personaFisica = new ClientePersonasFisicas();
			    personaFisica.mapper(dto);
			    session.save(personaFisica);
			    tx.commit();
			    try{
			    	ClientePersonasFisicasPkDTO res = findLastInsert();
			    	pk = new ClientePersonasFisicasPkDTO();
			    	pk.setIdCliente(res.getIdCliente());
			    	
			    }catch (DaoException es) {
					
			    	throw new HibernateException("Insersion: " +es.getMessage());
				}
				return pk;
			
				
			}

			
		});
	///	 return exito;
	} catch (final Exception e) {
		logger.error("Error: Insert ClientePersonasFisicasDao, cause: " + e.getMessage(), e);
		
	}
	return exito;
}

		
	/** 
	 * Updates a single row in the Activity table.
	 */
	
	@Override
	@Transactional
	public void update(final ClientePersonasFisicasDTO dto) throws DaoException
	{
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					ClientePersonasFisicasPkDTO pk = null;
					ClientePersonasFisicasDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = (ClientePersonasFisicasDTO) findByPrimaryKey(dto
								.getIdCliente()).get(0);
						pk = new ClientePersonasFisicasPkDTO();
						pk.setIdCliente(res.getIdCliente());
					} catch (DaoException es) {
						throw new HibernateException(
								"Update ClientePFisicaDao: "
										+ es.getMessage());
					}
					///aqui se actualizar los campos y con los de la entrada  
					//cuidado de actualiza todo.
					ClientePersonasFisicas persis = new ClientePersonasFisicas();
					persis.mapper(dto);
					persis.setIdCliente(res.getIdCliente());
					session.update(persis);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: Update ClientePfisicas, cause: " + e.getMessage(), e);
			throw new DaoException("Error: Update ClientePfisicas, cause: " + e.getMessage());
		}
	
		
	
	}

	/** 
	 * Deletes a single row in the Activity table.
	 */
	
	@Override
	@Transactional
	public void delete(final ClientePersonasFisicasPkDTO pk) throws DaoException
	{
		
		
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				    Transaction tx = session.beginTransaction();
				    ClientePersonasFisicas personaFisica = (ClientePersonasFisicas)
				    		session.load(ClientePersonasFisicas.class,pk.getIdCliente() );
				    session.delete(personaFisica);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: " + e.getMessage());
		}
	}
	
	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "ClientePersonasFisicas";
	}

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'id_cliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<ClientePersonasFisicasDTO> findByPrimaryKey(final int idCliente) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(new
					HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT pf.idCliente as idCliente, pf.aPaterno as aPaterno, pf.aMaterno as aMaterno," +
									" pf.nombres as nombres, pf.sexo as sexo, pf.fNacimiento as fNacimiento, pf.fInicioActividad as fInicioActividad, pf.curp as curp, pf.email as email," +
									" pf.tel1 as tel1, pf.ext1 as ext1 , pf.tel2 as tel2, pf.ext2 as ext2, pf.tipoRegimen as tipoRegimen, pf.tipoActividad as tipoActividad, pf.antecedentes as antecedentes," +
									" pf.recomienda as recomienda, pf.idClienteGeneral as idClienteGeneral FROM " + getTableName() + " pf WHERE pf.idCliente = :id_cliente");
							oQuery.setInteger("id_cliente", idCliente);
							List<ClientePersonasFisicasDTO> clientePersonasF = oQuery.setResultTransformer(Transformers.
									aliasToBean(ClientePersonasFisicasDTO.class))
									.list();									 
							return clientePersonasF;
						}
					});
				
		}
		catch (Exception e) {
			logger.error("Error: getClientePersonasFisicas, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getClientePersonasFisicas, cause: " + e.getMessage());
		}
		return clientePFisicas;
	}

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria ''.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<ClientePersonasFisicasDTO> findAll()
			throws DaoException{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
					
							Query oQuery = session.createQuery("SELECT pf.idCliente as idCliente, pf.aPaterno as aPaterno, pf.aMaterno as aMaterno," +
									" pf.nombres as nombres, pf.sexo as sexo, pf.fNacimiento as fNacimiento, pf.fInicioActividad as fInicioActividad, pf.curp as curp, pf.email as email," +
									" pf.tel1 as tel1, pf.ext1 as ext1 , pf.tel2 as tel2, pf.ext2 as ext2, pf.tipoRegimen as tipoRegimen, pf.tipoActividad as tipoActividad, pf.antecedentes as antecedentes," +
									" pf.recomienda as recomienda, pf.idClienteGeneral as idClienteGeneral FROM " + getTableName() +
									" pf ORDER BY pf.idCliente");
							List<ClientePersonasFisicasDTO> clientePersonasF = oQuery.setResultTransformer(Transformers.aliasToBean(
									ClientePersonasFisicasDTO.class))
									.list();
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " ORDER BY id_cliente", this);
		}
		catch (final Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'id_cliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereIdClienteEquals(final int idCliente)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT pf.idCliente as idCliente, pf.aPaterno as aPaterno, pf.aMaterno as aMaterno," +
									" pf.nombres as nombres, pf.sexo as sexo, pf.fNacimiento as fNacimiento, pf.fInicioActividad as fInicioActividad, pf.curp as curp, pf.email as email," +
									" pf.tel1 as tel1, pf.ext1 as ext1 , pf.tel2 as tel2, pf.ext2 as ext2, pf.tipoRegimen as tipoRegimen, pf.tipoActividad as tipoActividad, pf.antecedentes as antecedentes," +
									" pf.recomienda as recomienda, pf.idClienteGeneral as idClienteGeneral FROM " + getTableName() +
									" pf WHERE pf.idCliente = :idCliente ORDER BY pf.idCliente");
							oQuery.setInteger("idCliente", idCliente);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
									ClientePersonasFisicasDTO.class)).list().get(0);
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE id_cliente = ? ORDER BY id_cliente", this,idCliente);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'aPaterno = :apaterno'.

	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereApaternoEquals(final String apaterno) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(new 
					HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno," +
									" nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email," +
									" tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes," +
									" recomienda FROM " + getTableName() +
									" WHERE aPaterno = aPatern ORDER BY aPaterno");
							oQuery.setString(0,apaterno);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE aPaterno = ? ORDER BY aPaterno", this,apaterno);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'aMaterno = :amaterno'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereAmaternoEquals(final String amaterno)
			throws DaoException {
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo," +
									" fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen," +
									" tipoActividad, antecedentes, recomienda FROM " + getTableName() + 
									" WHERE aMaterno = :aMatern ORDER BY aMaterno");
							oQuery.setString(0, amaterno);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE aMaterno = ? ORDER BY aMaterno", this,amaterno);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'nombres = :nombres'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereNombresEquals(final String nombres)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, " +
									"fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen," +
									" tipoActividad, antecedentes, recomienda FROM " + getTableName() + 
									" WHERE nombres = :name ORDER BY nombres");
							oQuery.setString(0, nombres);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;

						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE nombres = ? ORDER BY nombres", this,nombres);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'sexo = :sexo'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereSexoEquals(final String sexo) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, " +
									"fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen," +
									" tipoActividad, antecedentes, recomienda FROM " + getTableName() + 
									" WHERE sexo = :sex ORDER BY sexo");
							oQuery.setString(0, sexo);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE sexo = ? ORDER BY sexo", this,sexo);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'fNacimiento = :fnacimiento'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereFnacimientoEquals( final Date fnacimiento)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE fNacimiento = :fnac ORDER BY fNacimiento");
							oQuery.setDate(0, fnacimiento);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE fNacimiento = ? ORDER BY fNacimiento", this,fnacimiento);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
		
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'fInicioActividad = :finicioactividad'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereFinicioactividadEquals(final Date finicioactividad)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE fInicioActividad = :fInicio ORDER BY fInicioActividad");
							oQuery.setDate(0, finicioactividad);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, , curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE fInicioActividad = ? ORDER BY fInicioActividad", this,finicioactividad);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'rfc = :rfc'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereRfcEquals(final String rfc)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE rfc = :rFc ORDER BY rfc");
							oQuery.setString(0, rfc);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE rfc = ? ORDER BY rfc", this,rfc);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'curp = :curp'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereCurpEquals(final String curp)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE curp = :cUrp ORDER BY curp");
							oQuery.setString(0, curp);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE curp = ? ORDER BY curp", this,curp);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'email = :email'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereEmailEquals(final String email)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE email = eMail ORDER BY email");
							oQuery.setString(0, email);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE email = ? ORDER BY email", this,email);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'tel1 = :tel1'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereTel1Equals(final String tel1) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE tel1 = :tEl ORDER BY tel1");
							oQuery.setString(0, tel1);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE tel1 = ? ORDER BY tel1", this,tel1);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'ext1 = :ext1'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereExt1Equals(final String ext1)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE ext1 = eXt ORDER BY ext1");
							oQuery.setString(0, ext1);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE ext1 = ? ORDER BY ext1", this,ext1);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'tel2 = :tel2'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereTel2Equals(final String tel2)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE tel2 = :tel ORDER BY tel2");
							oQuery.setString(0, tel2);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE tel2 = ? ORDER BY tel2", this,tel2);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'ext2 = :ext2'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereExt2Equals(final String ext2) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE ext2 = :ext ORDER BY ext2");
							oQuery.setString(0, ext2);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE ext2 = ? ORDER BY ext2", this,ext2);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'tipoRegimen = :tipoRegimen'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereTipoRegimenEquals(final String tipoRegimen) 
			throws DaoException {
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE tipoRegimen = :tipoReg ORDER BY tipoRegimen");
							oQuery.setString(0, tipoRegimen);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE tipoRegimen = ? ORDER BY tipoRegimen", this,tipoRegimen);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'tipoActividad = :tipoActividad'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereTipoActividadEquals(final String tipoActividad)
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE tipoActividad = :act ORDER BY tipoActividad");
							oQuery.setString(0, tipoActividad);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE tipoActividad = ? ORDER BY tipoActividad", this,tipoActividad);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'antecedentes = :antecedentes'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereAntecedentesEquals(final String antecedentes)
			throws DaoException {
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE antecedentes = ant ORDER BY antecedentes");
							oQuery.setString(0, antecedentes);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});		
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE antecedentes = ? ORDER BY antecedentes", this,antecedentes);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'recomienda = :recomienda'.
	 
	@Transactional
	public List<ClientePersonasFisicasDTO> findWhereRecomiendaEquals(final String recomienda) 
			throws DaoException	{
		List<ClientePersonasFisicasDTO> clientePFisicas = null;
		try {
			clientePFisicas = (List<ClientePersonasFisicasDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento," +
									" fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad," +
									" antecedentes, recomienda FROM " + getTableName() +
									" WHERE recomienda = ;rec ORDER BY recomienda");
							oQuery.setString(0, recomienda);
							List<ClientePersonasFisicasDTO> clientePersonasF = (List<ClientePersonasFisicasDTO>)
									oQuery.setResultTransformer(Transformers.aliasToBean(
											ClientePersonasFisicasDTO.class));
							return clientePersonasF;
						}
					});		
//			return jdbcTemplate.query("SELECT id_cliente, aPaterno, aMaterno, nombres, sexo, fNacimiento, fInicioActividad, rfc, curp, email, tel1, ext1, tel2, ext2, tipoRegimen, tipoActividad, antecedentes, recomienda FROM " + getTableName() + " WHERE recomienda = ? ORDER BY recomienda", this,recomienda);
		}
		catch (Exception e) {
			logger.error("Error: CLIENTE_PERSONAS_FISICAS, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return clientePFisicas;
	}	 */

	/** 
	 * Returns the rows from the CLIENTE_PERSONAS_FISICAS table that matches the specified primary-key value.
	
	public ClientePersonasFisicasDTO findByPrimaryKey(ClientePersonasFisicasPkDTO pk) throws DaoException
	{
		return findByPrimaryKey( pk.getIdCliente() );
	} */

	
	@Override
	public ClientePersonasFisicasPkDTO findLastInsert() throws DaoException {
		ClientePersonasFisicasPkDTO personaFisica = null;
		try {
			personaFisica = (ClientePersonasFisicasPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(c.idCliente) as idCliente FROM "
											+ getTableName()
											+ " c ");
							
							ClientePersonasFisicasPkDTO personaFisicaDto = (ClientePersonasFisicasPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(ClientePersonasFisicasPkDTO.class)).list().get(0);
							return personaFisicaDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: Cliente persoan física, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return personaFisica;

	}
	
	
	//RNM
	
	/** 
	 * Returns all rows from the CLIENTE_PERSONAS_FISICAS table that match the criteria 'id_clienteGeneral = :idClienteGeneral'.
	 */
	@Override
	@Transactional
	public ClientePersonasFisicasDTO findByIdClienteGeneral(final int idClienteGeneral) 
			throws DaoException	{
		ClientePersonasFisicasDTO clientePFisicas = null;
		try {
			clientePFisicas = (ClientePersonasFisicasDTO)getHibernateTemplate().execute(new
					HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						  
							Query oQuery = session.createQuery("SELECT pf.idCliente as idCliente, pf.aPaterno as aPaterno, pf.aMaterno as aMaterno," +
									" pf.nombres as nombres, pf.sexo as sexo, pf.fNacimiento as fNacimiento, pf.fInicioActividad as fInicioActividad, pf.curp as curp, pf.email as email," +
									" pf.tel1 as tel1, pf.ext1 as ext1 , pf.tel2 as tel2, pf.ext2 as ext2, pf.tipoRegimen as tipoRegimen, pf.tipoActividad as tipoActividad, pf.antecedentes as antecedentes," +
									" pf.recomienda as recomienda, pf.idClienteGeneral as idClienteGeneral FROM " + getTableName() + " pf WHERE pf.idClienteGeneral = :id_clienteGeneral");
							oQuery.setInteger("id_clienteGeneral", idClienteGeneral);
							ClientePersonasFisicasDTO clientePersonasF = (ClientePersonasFisicasDTO) oQuery.setResultTransformer(Transformers.
									aliasToBean(ClientePersonasFisicasDTO.class))
									.list().get(0);									 
							return clientePersonasF;
						}
					});
				
		}
		catch (Exception e) {
			logger.error("Error: getClientePersonasFisicas, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getClientePersonasFisicas, cause: " + e.getMessage());
		}
		return clientePFisicas;
	}
	
	@SuppressWarnings("unchecked")
	public List<ClientePersonasFisicasDTO> busquedaPorPalabra(final String palabra) throws DaoException{
		List<ClientePersonasFisicasDTO> listaClientes = null;
		try {
			listaClientes = (List<ClientePersonasFisicasDTO>) getHibernateTemplate().execute(new HibernateCallback() {
				
				@Override
				public Object doInHibernate(Session session) throws HibernateException,
						SQLException {
					Query oQuery = session.createQuery("SELECT pf.aPaterno as aPaterno, pf.aMaterno as aMaterno," +
									" pf.nombres as nombres, pf.idClienteGeneral as idClienteGeneral FROM "
							+ getTableName() + " pf WHERE pf.aPaterno || pf.aMaterno || pf.nombres like :palabraBusqueda");
					oQuery.setString("palabraBusqueda", "%" + palabra + "%");
					List<ClientePersonasFisicasDTO> listaResultado = oQuery.setResultTransformer(Transformers.
							aliasToBean(ClientePersonasFisicasDTO.class)).list();
					return listaResultado;
				}
			});
		} catch (Exception e) {
			 log.debug("Error en busqueda General, causa: " + e.getCause());
		}
		return listaClientes;
	}


}
